﻿using GXRTBTC.PublicClassLibrary.Model;
using IoTSharp.Data.Taos;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;

namespace GXRTBTC.PublicClassLibrary.Util
{
    /// <summary>
    /// 分页查询
    /// </summary>
    public class PageUtil
    {
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static PageResult GetPageDatas(DbConnection dbConnection, string sql, int pageIndex, int pageSize)
        {
            return GetPageDatas(dbConnection, sql, new Page() { pageIndex = pageIndex, pageSize = pageSize });
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="dbConnection"></param>
        /// <param name="sql"></param>
        /// <param name="page"></param>
        /// <returns></returns>
        public static PageResult GetPageDatas(DbConnection dbConnection, string sql, Page page)
        {
            PageResult pageResult = new PageResult() { page = page };
            var sw = Stopwatch.StartNew();
            try
            {
                //默认sql
                string dataSql = sql;
                string countSql = "";
                if (page.paging)
                {
                    sql = sql.Replace("  ", " ").Replace("  ", " ");
                    string tmpCountSql = sql.ToUpper().LastIndexOf("ORDER BY") > 0 ? sql.Substring(0, sql.ToUpper().LastIndexOf("ORDER BY")) : sql;
                    countSql = ("select count(1) from (" + tmpCountSql + ") tmpTable").Replace("  ", " ");

                    //起始行数
                    int start = (page.pageIndex - 1) * page.pageSize;

                    //MySqlConnection NpgsqlConnection
                    string connType = dbConnection.GetType().Name;
                    //PostgreSQL分页查询
                    if ("NpgsqlConnection".Equals(connType))
                    {
                        dataSql = sql + " limit " + page.pageSize + " offset " + start;
                    }
                    else if ("MySqlConnection".Equals(connType))
                    {
                        //MySql的分页查询和PostgreSQL分页查询一样写法
                        dataSql = sql + " limit " + page.pageSize + " offset " + start;
                    }
                    //达梦数据库
                    else if ("DmConnection".Equals(connType))
                    {
                        //达梦支持常用的分页方式，ROWNUM（Oracle）、TOP（SQL Server）、LIMIT（MySQL），多种方法都行
                        //有top分页写法（not in）、limit分页写法、伪列分页写法（rowid、rownum）
                        //limit写法
                        dataSql = sql + " limit " + start + "," + page.pageSize;
                    }
                    //默认是涛思的分页写法
                    else
                    {
                        dataSql = sql + " limit " + start + "," + page.pageSize;
                    }
                }

                //结果集
                //List<Dictionary<string, string>> data = DbUtil.ExecReader(dbConnection, dataSql);
                //改为直接查询，报错信息可直接返回
                if (dbConnection.State != ConnectionState.Open)
                {
                    dbConnection.Open();
                }
                DbCommand dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandTimeout = 7200;
                dbCommand.CommandText = dataSql;
                DbDataReader dataReader = dbCommand.ExecuteReader();
                //lsRS = dataReader.ToList<T>();
                List<Dictionary<string, string>> data = dataReader.ToJson().ToObject<List<Dictionary<string, string>>>();
                dataReader.Close();

                pageResult.datas = data;

                //总数
                pageResult.page.totalCount = page.paging ? DbUtil.GetExecInt(dbConnection, countSql) : data.Count;

                //分页时检查页码是否合法
                if (page.paging && pageResult.page.totalCount > 0 && pageResult.page.pageIndex > pageResult.page.totalPages)
                {
                    pageResult.message = "页码参数非法";
                }
            }
            catch (Exception e)
            {
                pageResult.result = false;
                pageResult.message = e.Message;
            }
            finally
            {
                pageResult.time = sw.ElapsedMilliseconds;
            }

            return pageResult;
        }
    }
}
